|
MySQL 月差问题
计算A日期和B日期之间相差几年，不满一年算一年：
解决方案：对比两日期的天数，如果数值不一样认为不满一个月，加一操作
数据库： SQL语句：（当前时间为2021-06-02）
SELECT
(
case
WHEN ( `b`.`LASTINSPECTTYPE` = '改造' ) THEN
(
CASE
WHEN ( (timestampdiff( MONTH, date_format( `b`.`remouldinspectdate`, '%Y-%m-%d' ), curdate()) mod 12 ) = 0 ) THEN
(
case
when (date_format(curdate(),'%e')!=date_format(b.remouldinspectdate,'%e') ) THEN ceiling( (timestampdiff(MONTH,date_format(b.remouldinspectdate,'%Y-%m-%d'),(curdate() + INTERVAL 1 YEAR )) / 12 ) )
ELSE ceiling( (timestampdiff(MONTH,date_format(b.remouldinspectdate,'%Y-%m-%d'),curdate()) / 12 ) )
END
)
ELSE ceiling( (timestampdiff(MONTH,date_format(b.remouldinspectdate,'%Y-%m-%d'),curdate()) / 12 ) )
END
)
WHEN ( `b`.`LASTINSPECTTYPE` = '安装' ) THEN
(
CASE
WHEN ( (timestampdiff( MONTH, date_format( `b`.`INSTALLINSPECTDATE`, '%Y-%m-%d' ), curdate()) mod 12 ) = 0 ) THEN
(
case
when (date_format(curdate(),'%e')!=date_format(b.INSTALLINSPECTDATE,'%e') ) THEN ceiling( (timestampdiff(MONTH,date_format(b.INSTALLINSPECTDATE,'%Y-%m-%d'),(curdate() + INTERVAL 1 YEAR )) / 12 ) )
ELSE ceiling( (timestampdiff(MONTH,date_format(b.INSTALLINSPECTDATE,'%Y-%m-%d'),curdate()) / 12 ) )
END
)
ELSE ceiling( (timestampdiff(MONTH,date_format(b.INSTALLINSPECTDATE,'%Y-%m-%d'),curdate()) / 12 ) )
END
)
ELSE ''
END
) AS `USEYEARS`
from helloelv b
结果：  符合要求 但如果遇到两个日期相同还是有误，需要添加 to_day 判断
SELECT
(
CASE
WHEN ( `b`.`LASTINSPECTTYPE` = '重大修理' ) THEN
(
CASE
WHEN ( b.remouldinspectdate IS NOT NULL ) THEN
(
CASE
WHEN ( timestampdiff( MONTH, date_format(b.remouldinspectdate,'%Y-%m-%d'),curdate()) |